package com.geping.etl.common.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.geping.etl.common.service.LogsSynService;
import com.geping.etl.common.util.JDBCUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFEvaluationWorkbook;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;

import com.geping.etl.common.entity.Logs;
import com.geping.etl.common.entity.Sys_Org;
import com.geping.etl.common.service.LogsService;
import com.geping.etl.common.service.Sys_OrgService;
import com.itextpdf.text.pdf.codec.Base64.OutputStream;

import net.sf.json.JSONObject;

@RestController
@Slf4j
public class LogsController {
	
	@Autowired
	private LogsService ls;
	
	@Autowired
	private Sys_OrgService sys_OrgService;

	@Autowired
	private LogsSynService logsSynService;
	
	DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
	DateFormat df2 = new SimpleDateFormat("yyyyMMdd HHmmss");
    //访问查看日志页面
	@RequestMapping(value="/showLogsUi",method=RequestMethod.GET)
	public ModelAndView showLogsUi(){
		ModelAndView model = new ModelAndView();
		List<Sys_Org> list = sys_OrgService.findAllSysOrg();
		model.addObject("list", list);
		model.setViewName("common/logs");
		return model;
	}
	
	
	//查询日志信息
	@RequestMapping(value="/showLogs",method=RequestMethod.POST)
	public void showLogs(HttpServletRequest request,HttpServletResponse response){
		PrintWriter out = null;
		try {
			response.setContentType("text/html");
		    response.setContentType("text/plain; charset=utf-8");
		    
		    String startDate = request.getParameter("startDate");       //开始时间
			String endDate = request.getParameter("endDate");           //结束时间
		    String logOrgCode = request.getParameter("logOrgCode");
		    
		    String currentPageNumber = request.getParameter("page");  //获取查询页数
		    String currentPageSize = request.getParameter("rows");   //获取每页条数
		    
		    int page = Integer.valueOf(currentPageNumber) - 1;
		    int size = Integer.valueOf(currentPageSize);
		    
		    Specification<Logs> querySpecifi = new Specification<Logs>() {
				@Override
				public Predicate toPredicate(Root<Logs> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
					List<Predicate> predicates = new ArrayList<>();
					
					if(startDate != null && !startDate.equals("")){
			            predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("logDate"),startDate + " 00:00:00"));
			        }
			        
			        if(endDate != null && !endDate.equals("")){
			            predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("logDate"),endDate + " 23:59:59"));
			        }
					
			        if(logOrgCode != null) {
			        	if(!logOrgCode.equals("all")) {
				        	predicates.add(criteriaBuilder.equal(root.get("logOrgCode"),logOrgCode));
				        }
			        }
			        
	                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
				}
	        };
	        
	        /**
	         * 多条件排序
	        List<Order> orders=new ArrayList<Order>();
	        orders.add(new Order(Direction.DESC,"a"));
	        orders.add(new Order(Direction.DESC,"b"));
	        */
	        
	        /**
	         * 单一条件排序
	         */
	        Sort sort = new Sort(Sort.Direction.DESC,"logDate");
	        
	        //Pageable 接口通常使用的其 PageRequest 实现类. 其中封装了需要分页的信息
			PageRequest pr = new PageRequest(page,size,sort);     //intPage从0开始
	        Page<Logs> gcPage =  ls.findAll(querySpecifi,pr);
		    JSONObject json = new JSONObject();
			out = response.getWriter();
			json.put("rows",gcPage.getContent());
			json.put("total",gcPage.getTotalElements());   // 将总页数传到页面上显示
			out.write(json.toString());
		} catch (IOException e){
			e.printStackTrace();
		}finally{
			if(out != null) {
				out.flush();
				out.close();
			}
		}
	}
	
	//导出日志
	@RequestMapping(value="/exportSys_Logs")
	public void exportSys_logs(HttpServletRequest request, HttpServletResponse response) {
		ServletOutputStream out = null;
		SXSSFWorkbook wb = null;
		JDBCUtils utils = new JDBCUtils();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String startDate = request.getParameter("startDate");
		String endDate = request.getParameter("endDate");
		String logOrgCode = request.getParameter("logOrgCode");
		if (endDate!=null && !"".equals(endDate)){
			endDate = endDate + " 23:59:59";
		}
		try {			
			request.setCharacterEncoding("utf-8");
			response.setContentType("text/html");
			response.setContentType("text/plain;charset=utf-8");
			response.setContentType("octets/stream");
			
			conn = utils.getConnection();
			StringBuffer sql = new StringBuffer();
			StringBuffer sql2 = new StringBuffer();
			sql.append("select * from sys_logs where 1=1");
			sql2.append("select count(*) from sys_logs where 1=1");
			if(startDate != null && !startDate.equals("")) {
				sql.append(" and log_date >= '" + startDate + "'");
				sql2.append(" and log_date >= '" + startDate + "'");
			}
			if(endDate != null && !endDate.equals("")) {
				sql.append(" and log_date <= '" + endDate + "'");
				sql2.append(" and log_date <= '" + endDate + "'");
			}
			if(!logOrgCode.equals("all")) {
				sql.append(" and log_org_code = '" + logOrgCode + "'");
				sql2.append(" and log_org_code = '" + logOrgCode + "'");
			}
			//TODO 待优化
			sql.append(" order by LOG_DATE DESC");
			rs = utils.query(conn, ps, rs, sql.toString());
			rs2 = utils.query(conn, ps, rs, sql2.toString());
			String[] headers = {"操作人","日志明细","日志类型","银行机构代码","日志生成日期"};
			String fileName = "日志" + df.format(new Date()) + ".xlsx";
			wb = new SXSSFWorkbook();
			response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"),"ISO8859-1"));
			// 设置字体样式
//			Font font = wb.createFont();
//			font.setFontHeightInPoints((short) 14);
//			font.setFontName("宋体");
//			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//			CellStyle style1 = wb.createCellStyle();
//			style1.setAlignment(CellStyle.ALIGN_CENTER);
//			style1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//			style1.setBorderTop(BorderStyle.THIN);
//			style1.setBorderBottom(BorderStyle.THIN);
//			style1.setBorderLeft(BorderStyle.THIN);
//			style1.setBorderRight(BorderStyle.THIN);
//			style1.setFont(font);

			Font font = wb.createFont();
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 10);
			font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
			CellStyle style = wb.createCellStyle();
			style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平对齐
			style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直对齐
			style.setBorderTop(BorderStyle.THIN);// 设置边框
			style.setBorderBottom(BorderStyle.THIN);
			style.setBorderLeft(BorderStyle.THIN);
			style.setBorderRight(BorderStyle.THIN);
			style.setFont(font);

//			CellStyle style = wb.createCellStyle();
//			style.setFont(font);

			// 设置单元格保留小数
			CellStyle cellStylePoint = wb.createCellStyle();
			DataFormat formatP = wb.createDataFormat();
			cellStylePoint.setDataFormat(formatP.getFormat("#,##0.00"));// 保留2位小数点

			SimpleDateFormat sdfn = new SimpleDateFormat("yyyy-MM-dd");
			// 设置单元格格式为日期格式
			CellStyle cellStyleDate = wb.createCellStyle();
			DataFormat formatD = wb.createDataFormat();
			cellStyleDate.setDataFormat(formatD.getFormat("m/d/yy"));
			
			int total = 0;
			if(rs2.next()) {
				total = rs2.getInt(1);
			}
			
			int sheetPageSize = 0;
			if (total >= 1048570) {
				sheetPageSize = 1048570; // 每页sheet显示的条数
			} else {
				sheetPageSize = total; // 每页sheet显示的条数
			}

			int sheetPage = 0;
			if (total == 0) {
				sheetPage = 1;
			} else {
				sheetPage = (total % sheetPageSize) == 0 ? total / sheetPageSize : (total / sheetPageSize + 1); // sheetPage是sheet的总数量
			}
			for (int j = 0; j < sheetPage; j++) {
				// 创建第一个工作表
				Sheet sheet = wb.createSheet("sheet" + (j + 1));
				SXSSFRow row = (SXSSFRow) sheet.createRow(0);
				// 创建标题行
				for (int i = 0; i < headers.length; i++) {
					SXSSFCell cell = row.createCell(i);
					cell.setCellStyle(style);
					cell.setCellValue(headers[i]);
					sheet.setColumnWidth(i, 30*256);
				}
				int i = j * sheetPageSize;
				while(rs.next()) {
					SXSSFRow rowPlus = (SXSSFRow) sheet.createRow((i % sheetPageSize) + 1);
					
					SXSSFCell cell0=rowPlus.createCell(0);

					cell0.setCellValue(rs.getString("login_id"));
					cell0.setCellStyle(style);
					
					SXSSFCell cell1=rowPlus.createCell(1);
					cell1.setCellValue(rs.getString("log_content"));
					cell1.setCellStyle(style);

					SXSSFCell cell2=rowPlus.createCell(2);
					cell2.setCellValue(rs.getString("log_type"));
					cell2.setCellStyle(style);

					SXSSFCell cell3=rowPlus.createCell(3);
					cell3.setCellValue(rs.getString("log_org_code"));
					cell3.setCellStyle(style);

					SXSSFCell cell4=rowPlus.createCell(4);
					cell4.setCellValue(rs.getString("log_date"));
					cell4.setCellStyle(style);
					i++;
				}
			out = response.getOutputStream();
			wb.write(out);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally{
			if(wb!=null){
				try {
					wb.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(out!=null){
				try {
					out.flush();
					out.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			utils.close(rs, ps, conn);
			utils.close(rs2, ps, conn);
		}
	}

	@RequestMapping(value="/synLogs")
	public void synLogs(HttpServletRequest request, HttpServletResponse response) {
		log.info("synLogs start");
		logsSynService.updateLogs();
		log.info("synLogs end");

	}
}
